from pymysql import *
import datetime


class partition_():
    def __init__(self, table, create_sql):
        try:
            self.conn = connect(host='10.10.1.1', user='ly', password='123', port=3306, db='ocr')
        except Exception as e:
            self.conn = connect(host='localhost', user='ly', password='123', port=3306, db='ocr')
        self.cursor = self.conn.cursor(cursor=cursors.DictCursor)  # 查询结果以字典方式返回
        self.create_sql = create_sql
        self.table = table
        self.drop_table()
        self.create_table()
        # self.insert_data()
        # self.one_insert()
        self.loop_insert()

    def drop_table(self):
        sql = f''' drop table if exists {self.table} '''
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            print("表格清理成功")
        except Exception as e:
            self.conn.rollback()
            print(e)

    def create_table(self):
        try:
            self.cursor.execute(self.create_sql)
            self.conn.commit()
            print("表创建成功")
        except Exception as e:
            self.conn.rollback()
            print(e)

    def insert_data(self):
        data_list = [(1,'张三','张','2015-05-04', 1), (2,'李四','李','2016-10-01',5), (3,'王五','王','2016-11-14',10), (4,'赵六','赵','2017-08-24',15), (5,'田七','田','2018-05-20',20)]
        # val = '%s, ' * (len(data_list[0]) - 1) + '%s'
        for i in data_list:
            sql = f'insert into ly1(id,fname,lname,hired,store_id) values {i}'
            try:
                self.cursor.execute(sql)
                self.conn.commit()
                print("数据批插入成功")
            except Exception as e:
                print("输入插入有异常", e)
                self.conn.rollback()  # 异常回转, 所有数据插入无效

    def one_insert(self, i, val = None):
        # sql = f'''--  insert into {self.table}(id,fname,lname,hired,store_id) values (1, '张三', '张', '2015-05-04', {i}) '''
        sql = f''' insert into {self.table} values ({i}, '{val}') '''
        # print(sql)
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            # print("单条数据插入成功")
        except Exception as e:
            self.conn.rollback()
            print(e)

    def loop_insert(self):
        import random
        for i in range(1000):
            insert_date = f'''{random.randint(2019, 2021)}-{random.randint(1, 12)}-{random.randint(1, 30)}'''    # 2009-10-01 00:00:00
            self.one_insert(random.randint(0, 10000), str(insert_date))


if __name__=='__main__':
    sql1 = ''' 
            create table ly1( 
                            id int not null,
                            fname varchar(30),
                            lname varchar(30),
                            hired date not null default '1970-01-01',
                            separated date not null default '9999-12-31',
                            job_code int not null default 0,
                            store_id int not null default 0
                        )engine=myisam default charset=utf8
                        partition by range(store_id)(
                            partition p0 values less than (6),
                            partition p1 values less than (11),
                            partition p2 values less than (16),
                            partition p3 values less than (23),
                            partition p5 values less than (5000),
                            partition p6 values less than MAXVALUE
                        ) 
                        '''
    sql2 = '''create table ly2(
              report_id int not null,
              report_status varchar(20) not null,
              report_updated timestamp not null default current_timestamp on update current_timestamp
            )
            partition by range(unix_timestamp(report_updated))(
              partition p0 values less than (unix_timestamp('2018-01-01 00:00:00')),
              partition p1 values less than (unix_timestamp('2018-04-01 00:00:00')),
              partition p2 values less than (unix_timestamp('2018-07-01 00:00:00')),
              partition p3 values less than (unix_timestamp('2018-10-01 00:00:00')),
              partition p4 values less than (unix_timestamp('2019-01-01 00:00:00')),
              partition p5 values less than (unix_timestamp('2019-04-01 00:00:00')),
              partition p6 values less than (unix_timestamp('2019-07-01 00:00:00')),
              partition p7 values less than (unix_timestamp('2019-10-01 00:00:00')),
              partition p8 values less than (unix_timestamp('2020-01-01 00:00:00')),
              partition p9 values less than maxvalue
            );
            '''

    sql3 = '''create table ly3(
              id int not null,
              joined date not null
            )
            partition by range columns(joined)(
              partition p0 values less than ('1960-01-01'),
              partition p1 values less than ('1970-01-01'),
              partition p2 values less than ('1980-01-01'),
              partition p3 values less than ('1990-01-01'),
              partition p4 values less than maxvalue
            )
            '''

    sql4 = '''create table ly4(
              id int not null,
              joined date not null
            )engine=myisam default charset=utf8
              partition by range(year(joined))(
              partition p0 values less than ('2018'),
              partition p1 values less than ('2019'),
              partition p2 values less than ('2020'),
              partition p3 values less than ('2021'),
              partition p4 values less than MAXVALUE
            )
            '''

    sql5 = '''create table ly5(
                  id int not null,
                  year_test date not null default '9999-12-31'
                )engine=myisam default charset=utf8
                partition by range(year(year_test))(
                  partition p0 values less than (2019),
                  partition p1 values less than (2020),   
                  partition p2 values less than (2021),
                  partition p3 values less than (2022),
                  partition p4 values less than MAXVALUE
                );
                '''

    obj = partition_('ly5', sql5)





